package lookups.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import lookups.entity.TypeOfBusiness;

public class TypeOfBusinessDAO {
	
	private String DATABASE_NAME="MockProject";
	private String TABLE_NAME="TypeOfBusiness";
	private String USERNAME="sa";
	private String PASSWORD="12345678";
	
	private ArrayList<TypeOfBusiness> listTypeOfBusiness;
	private int totalResult;

	public ArrayList<TypeOfBusiness> getListTypeOfBusiness() {
		return listTypeOfBusiness;
	}
		
	public int getTotalResult() {
		return totalResult;
	}

	public void setTotalResult(int totalResult) {
		this.totalResult = totalResult;
	}

	
	public void setListTypeOfBusiness(ArrayList<TypeOfBusiness> listTypeOfBusiness) {
		this.listTypeOfBusiness = listTypeOfBusiness;
	}

	public ArrayList<TypeOfBusiness> getListTypeOfBusiness(String SICCode, String businessName,
														int pageNum,int pageSize){
//		boolean addAND=false;
		Connection con = null;
		int rowStart=(pageNum-1)*pageSize+1;
		int rowEnd=rowStart+pageSize-1;
		
		String sql="SELECT typeId,SICCode,businessName "
		          +" FROM ("
			            +" SELECT SICCode, businessName,typeId,ROW_NUMBER() OVER (ORDER BY typeId) AS RowNum "
			            +" FROM "+TABLE_NAME+" a "
			      		+" WHERE ";
//        if(businessName !=null && businessName.length()>0){
        						sql+=" a.businessName LIKE '%"+businessName+"%' ";
//        						addAND=true;
//        }
//        if(SICCode !=null && SICCode.length()>0){
//        	if(addAND){
        						sql+=" AND a.SICCode LIKE '%"+SICCode+"%' ";
//        	}
//        	else {
//        						sql+=" a.SICCode LIKE '%"+SICCode+"%' ";
//        	}
//        }
        		sql+=") AS TOB "
        		+" WHERE TOB.RowNum BETWEEN "+rowStart+" AND "+rowEnd+" ";
        
        System.out.println("sql= "+sql);
               
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	
        	listTypeOfBusiness=new ArrayList<TypeOfBusiness>();
        	
        	while(rs.next()){
        		int typeID=rs.getInt(1);
        		String SICCodes=rs.getString(2);
        		String businessNames=rs.getString(3);
        	
        		TypeOfBusiness a=new TypeOfBusiness(typeID,SICCodes,businessNames);        		
        		listTypeOfBusiness.add(a);
        		System.out.println(SICCodes);
        	}
        	return listTypeOfBusiness;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
        }
        
		return null;
	}
        
    public int getTotalTypeOfBusiness(String SICCode, String businessName){
//		boolean addAND=false;
		Connection con = null;
    		
		String sql="SELECT SICCode,businessName "+
					"FROM "+TABLE_NAME+" a " +
					"WHERE ";
		
//        if(businessName !=null && businessName.length()>0){
        	sql+="a.businessName LIKE '%"+businessName+"%' ";
//        	addAND=true;
//        }
//        if(SICCode !=null && SICCode.length()>0){
//        	if(addAND){
        		sql+=" AND a.SICCode LIKE '%"+SICCode+"%' ";
//        	}
//        	else {
//        		sql+=" a.SICCode LIKE '%"+SICCode+"%' ";
//        	}
//        }
        
        System.out.println("sql total= "+sql);
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	int count=0;
        	while(rs.next()){
        		count++;
        	}
        	return count;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
            
    	return 0;
    }
}
